package com.pb.news.dao.impl;

import com.pb.news.dao.BaseDao;
import com.pb.news.dao.NewsDao;
import com.pb.news.entity.News;
import com.pb.news.util.Page;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * NewsDaoImpl--BDQN_WEB_NEWS
 *
 * @author: Jovi
 * @createTime: 2018-03-21 16:13
 **/
public class NewsDaoImpl extends BaseDao implements NewsDao {

    public static void main(String[] args) {
        NewsDaoImpl newsDaoImpl = new NewsDaoImpl();


        // List<News> newsLists = newsDaoImpl.getNewsLists();
        // for (News news : newsLists) {
        //     System.out.println(news);
        // }

        int total = newsDaoImpl.getTotalCount();
        System.out.println("总数量" + total);

        try {
            List<News> pageNewsList = newsDaoImpl.getPageNewsList(1, 2);
            for (News news : pageNewsList) {
                System.out.println(news);

            }

            int totalCountProc = newsDaoImpl.getTotalCountProc();
            System.out.println("总数量："+totalCountProc);

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public List<News> getNewsLists() {
        List<News> newsList = new ArrayList<>();

        String sql = "select * from news_detail";
        Object[] params = {};


        ResultSet rs = executeSql(sql, params);
        try {
            while (rs.next()) {

                int id = rs.getInt("id");
                String title = rs.getString("title");
                String summary = rs.getString("summary");
                String content = rs.getString("content");
                String picpath = rs.getString("picpath");
                String author = rs.getString("author");

                Timestamp timestamp = rs.getTimestamp("createDate");//日期在数据库是Date类型， ResultSet获取的时候就应该用Timestamp来获取

                News news = new News();

                news.setId(id);
                news.setTitle(title);
                news.setSummary(summary);
                news.setContent(content);
                news.setPicPath(picpath);
                news.setAuthor(author);
                news.setCreateDate(timestamp);

                newsList.add(news);

                // System.out.println(id + "\t" + title + "\t" + summary + "\t" + content + "\t" + picpath + "\t" + author + "\t" + timestamp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeResource();
        }

        return newsList;

    }

    public boolean add(News news) {

        String sql = "insert INTO news_detail(id,categoryId,title,summary,content,createDate,author，picPath) VALUES (SEQ_NEWS.nextval ,?,?,?,?,?,?,?)";

        Object[] params = {news.getCategoryId(), news.getTitle(), news.getSummary(), news.getContent(), new Timestamp(news.getCreateDate().getTime()), news.getAuthor(), news.getPicPath()};

        int i = executeUpdate(sql, params);

        if (i > 0) {
            System.out.println("插入新闻成功");
        }

        closeResource();

        return true;

    }

    public boolean delete(int id) {

        String sql = "delete from news_detail where id = ?";

        Object[] params = {id};

        int i = executeUpdate(sql, params);
        if (i > 0) {
            System.out.println("删除成功");
        }

        closeResource();

        return true;

    }

    public boolean update(News news) {

        String sql = "update SHOP_DEV_U.NEWS_DETAIL set AUTHOR=?, CREATEDATE=?," +
                " TITLE=?, SUMMARY=?, CONTENT=?, MODIFYDATE=?, PICPATH=?, CATEGORYID=? where , ID=?";
        Object[] params = {news.getAuthor(), news.getCreateDate(), news.getTitle(),
                news.getSummary(), news.getContent(), news.getModifyDate(), news.getPicPath(), news.getCategoryId(), news.getId()};
        int i = executeUpdate(sql, params);
        if (i > 0) {
            System.out.println("新闻修改成功");
        }

        closeResource();

        return true;
    }

    @Override
    public News getNewsById(int id) {

        News news = null;

        String sql = "select * from news_detail where id=" + id;

        Object[] params = {};

        ResultSet rs = this.executeSql(sql, params);
        try {
            if (rs.next()) {
                int categoryId = rs.getInt("categoryId");
                String title = rs.getString("title");
                String summary = rs.getString("summary");
                String content = rs.getString("content");
                String picPath = rs.getString("picPath");
                String author = rs.getString("author");
                Timestamp createdate = rs.getTimestamp("createdate");

                news = new News();
                news.setId(id);
                news.setCategoryId(categoryId);
                news.setTitle(title);
                news.setSummary(summary);
                news.setContent(content);
                news.setPicPath(picPath);
                news.setAuthor(author);
                news.setCreateDate(createdate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeResource();
        }

        return news;
    }

    @Override
    public int getTotalCount() {


        int totalCount = 0;
        String sql = "select count(*) from news_detail";
        Object[] params = {};

        ResultSet rs = this.executeSql(sql, params);

        try {
            while (rs.next()) {
                totalCount = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return totalCount;
    }

    @Override
    public List<News> getPageNewsList(int pageNo, int pageSize) throws SQLException {

        List<News> list = new ArrayList<>();
        String sql = "select id,title,author,createdate from (select id,title,author,createdate ,rownum rn from news_detail) a  where a.rn >= ? and a.rn <= ?";

        Page page = new Page();
        page.setCurrPageNo(pageNo);//设置当前页码
        page.setPageSize(pageSize);//设置每页显示记录数

        /* 计算Sql语句的其实记录数以及结束记录数的行数 */
        int startRow = page.getStartRow();
        int endRow = page.getEndRow();

        Object[] params = {startRow, endRow};
        ResultSet rs = this.executeSql(sql, params);
        while (rs.next()) {
            int id = rs.getInt("id");
            String title = rs.getString("title");
            String author = rs.getString("author");
            Date createdate = rs.getDate("createdate");
            News newInfo = new News();
            newInfo.setId(id);
            newInfo.setTitle(title);
            newInfo.setAuthor(author);
            newInfo.setCreateDate(new Timestamp(createdate.getTime()));

            list.add(newInfo);
        }

        return list;
    }

    @Override
    public int getTotalCountProc() {
        int totalCount = 0;

        CallableStatement proc;
        String sql = "{call getNewsCount(?)}";

        getConnection();
        try {
            proc = conn.prepareCall(sql);
            proc.registerOutParameter(1,Types.INTEGER);
            proc.execute();
            totalCount = proc.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return totalCount;
    }

}
